package com.wrox;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.Collections;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class EmployeeData {

  public static Employee getEmployee(String username, DataSource dataSource)
    throws Exception {

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Employee employee = null;
    try {

      conn = dataSource.getConnection();
      stmt = conn.prepareStatement("select * from employees where username=?");
      stmt.setString(1, username);
      rs = stmt.executeQuery();

      if ( rs.next() ) {
        employee = new Employee();
        employee.setUsername(rs.getString(1));
        employee.setPassword(rs.getString(2));
        employee.setRoleid(rs.getInt(3));
        employee.setName(rs.getString(4));
        employee.setPhone(rs.getString(5));
        employee.setEmail(rs.getString(6));
        employee.setDepid(rs.getInt(7));
        
        }
      else {

        throw new Exception("Employee " + username + " not found!");
      }
    }
    finally {

      if ( rs != null ) {

        rs.close();
      }
      if ( stmt != null ) {

        stmt.close();
      }
      if ( conn != null ) {

        conn.close();
      }
    }
    return employee;
  }

  public static ArrayList getEmployees(DataSource dataSource) {

    Employee employee = null;
    ArrayList employees = null;
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    boolean dataFoundFlag = false;

    try {

      conn = dataSource.getConnection();
      stmt = conn.createStatement();
      rs =
        stmt.executeQuery("select * from employees, roles, "
        + "departments where employees.roleid=roles.roleid "
        + "and employees.depid=departments.depid");

      while (rs.next()) {
    	  dataFoundFlag =true;
        employee = new Employee();

        employee.setUsername(rs.getString("username"));
        employee.setName(rs.getString("name"));
        employee.setRolename(rs.getString("rolename"));
        employee.setPhone(rs.getString("phone"));
        employee.setEmail(rs.getString("email"));
        employee.setRoleid(new Integer(rs.getInt("roleid")));
        employee.setDepid(new Integer(rs.getInt("depid")));
        employee.setDepartment(rs.getString("depname"));

        employees.add(employee);

        System.err.println("Username : "
          + employee.getUsername()
          + " Department : " + rs.getString("depname"));
      }
    }
    catch ( SQLException e ) {

      System.err.println(e.getMessage());
    }
    finally {

      if ( rs != null ) {

        try {

          rs.close();
        }
        catch ( SQLException sqle ) {

          System.err.println(sqle.getMessage());
        }
        rs = null;
      }
      if ( stmt != null ) {

        try {

          stmt.close();
        }
        catch ( SQLException sqle ) {

          System.err.println(sqle.getMessage());
        }
        stmt = null;
      }
      if ( conn != null ) {

        try {

          conn.close();
        }
        catch ( SQLException sqle ) {

          System.err.println(sqle.getMessage());
        }
        conn = null;
      }
    }
    if(dataFoundFlag) return  employees;
    else
    return (ArrayList) Collections.EMPTY_LIST;
  }

  public static void removeEmployee(String username, DataSource dataSource)
  throws Exception {

  Connection conn = null;
  PreparedStatement stmt = null;
  ResultSet rs = null;

  try {

    conn = dataSource.getConnection();
    stmt = conn.prepareStatement("delete from employees where username=?");
     stmt.setString(1,username);
    stmt.execute();
  }
  finally {

    if ( rs != null ) {

      rs.close();
    }
    if ( stmt != null ) {

      stmt.close();
    }
    if ( conn != null ) {

      conn.close();
    }
  }
}
  public static void addEmployee(Employee employee, DataSource dataSource)
  throws Exception {

  Connection conn = null;
  
  ResultSet rs = null;
  PreparedStatement ps=null;
  try 
  {

    conn = dataSource.getConnection();
    ps = conn.prepareStatement("insert into employees values(?,?,?,?,?,?,?)");
    

    ps.setString(1,employee.getUsername());
    ps.setString(2,employee.getPassword());
    ps.setInt(3,employee.getRoleid());
    ps.setString(4,employee.getName());
    ps.setString(5,employee.getPhone());
    ps.setString(6,employee.getEmail());
    ps.setInt(7,employee.getDepid());
    

    ps.execute();
    
    
          
  }
  catch(Exception e)
  {
  	e.printStackTrace();
  }
  finally {

    if ( rs != null ) {

      rs.close();
    }
    if ( ps != null ) {

      ps.close();
    }
    if ( conn != null ) {

      conn.close();
    }
  }
}
  
  public static void updateEmployee(Employee employee, DataSource dataSource)
  throws Exception {

  Connection conn = null;
   PreparedStatement ps= null;
  ResultSet rs = null;
  try {

    conn = dataSource.getConnection();
    ps=conn.prepareStatement("update employees set roleid=? where phone=?");

  ps.setInt(1,employee.getRoleid());
  ps.setString(2,employee.getPhone());
  ps.execute();
  }
  finally {

    if ( rs != null ) {

      rs.close();
    }
    if ( ps != null ) {

      ps.close();
    }
    if ( conn != null ) {

      conn.close();
    }
  }
}
}
